using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace project
{
    public partial class Payment_Retailer : Form
    {        
        static OleDbConnection  conn;
        static string query;
        static OleDbCommand command;
        String[] mname =new String[100];
        static int total_amount;
        
        public Payment_Retailer()
        {
            InitializeComponent();
            total_amount=0;
            command = new OleDbCommand();
            foreach(Retailer_DBClass obj in Form3.retailer_purchaselist)
            {
                total_amount+=(obj.price*obj.qty);
            }
            textBox1.Text = total_amount.ToString();
            textBox2.Text = Form3.retailer_purchaselist[0].RNAME;
            dbconnection();
            update_datagrid();

        }

        private void dbconnection()
        {
            try
            {
                conn = DBComponent.Mag_DealerDB.dbconnection();
                conn.Open();
                command = new OleDbCommand();
                command.Connection = conn;

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

        }
        private void update_datagrid()
        {
            //updates the data grid which displays the purchase details
            try
            {
                query = "delete * from temp;";
                
                command.CommandText = query;
                command.Connection = conn;
                command.ExecuteNonQuery();
                int sno = 0;
                foreach (Retailer_DBClass obj in Form3.retailer_purchaselist)
                {
                    sno++;
                    query = "insert into temp(SNO,MNAME,MDATE,MPRICE,QTY,TOTAL) VALUES('"+sno+"','" + obj.MNAME + "','" + obj.MDATE + "','" + obj.price + "','" + obj.qty + "','" + (obj.price*obj.qty) + "');";
                    command.CommandText = query;
                    command.ExecuteNonQuery();
                }
                String s;
                s = "Select * from temp";
                OleDbCommand cmd1 = new OleDbCommand(s, conn);
                OleDbDataAdapter da = new OleDbDataAdapter(s, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "temp");
                tempDataGridView.DataSource = ds;
                tempDataGridView.DataMember = "temp";
                tempBindingSource.ResetBindings(false);
                this.Refresh();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

     

        private void Payment_Retailer_FormClosed(object sender, FormClosedEventArgs e)
        {
            if (Application.OpenForms.Count == 1)
            {
                Program.main_form.Show();
            }
        }

        private void Payment_Retailer_Load(object sender, EventArgs e)
        {
            this.tempTableAdapter.Fill(this.mag_dealerDataSet.temp);
        }



        private void Pay_Button_Click(object sender, EventArgs e)
        {
            Pay_Retailer pay = new Pay_Retailer();
            pay.curr_amt = int.Parse(textBox1.Text);
            Program.Invoke(this, "project.Pay_Retailer");
        }


        private void AddToCredit_Button_Click_1(object sender, EventArgs e)
        {
            SaveRetailerReceipt();
            UpdateRetailerSold();
            UpdateMagazineSold();
            UpdateRetailerCredit();
            UpdateStockDate();
            Program.InvokeModal(this, "project.Retailer_Receipt");
        }

private void SaveRetailerReceipt()
{
    Retailer_Receipt_Object ret_rec = new Retailer_Receipt_Object();
    ret_rec.amount_paid = 0;
    query = "select AMOUNT_DUE from retailer_credit where RNAME='" + Form3.retailer_purchaselist[0].RNAME + "';";
    command.CommandText = query;
    OleDbDataReader reader = command.ExecuteReader();
    int amount_due = 0;
    while (reader.Read())
    {
        amount_due = int.Parse(reader[0].ToString());
    }
    ret_rec.current_due = amount_due+int.Parse(textBox1.Text) - ret_rec.amount_paid;
    ret_rec.rname = Form3.retailer_purchaselist[0].RNAME;
    ret_rec.trans_date = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
    Form3.ret_receipt = ret_rec;
}


         

        public static void UpdateRetailerSold()
        {
            query = "insert into retailer_sold(CURR_DATE,RNAME,TOTAL_PURCHASE_AMOUNT) VALUES('" + DateTime.Now + "','" + Form3.retailer_purchaselist[0].RNAME + "','" + total_amount + "');";
            command = new OleDbCommand(query, conn);
            command.ExecuteNonQuery();
        }

        public static void UpdateMagazineSold()
        {
            int sno = 0;
            query = "SELECT TOP 1 * from retailer_sold Order By S_NO DESC";
            command.CommandText = query;
            OleDbDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                sno = int.Parse(reader["S_NO"].ToString());
            }


            Console.WriteLine(sno);
            reader.Close();
            Insert q = new Insert("magazine_sold");
            foreach (Retailer_DBClass obj in Form3.retailer_purchaselist)
            {                
                query = "insert into magazine_sold(CURR_DATE,MNAME,MDATE,RNAME,QUANTITY,PRICE,TOTAL,S_NO) VALUES('" + DateTime.Now + "','" + obj.MNAME + "','" + obj.MDATE + "','" + obj.RNAME + "','" + obj.qty + "','" + obj.price + "','" + (obj.price * obj.qty) + "','" + sno + "');";                
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
        }

        private void UpdateRetailerCredit()
        {
            int credit_amt = 0;
            int amount_due = 0;
            query = "select CREDIT_AMOUNT,AMOUNT_DUE from retailer_credit where RNAME='" + Form3.retailer_purchaselist[0].RNAME + "';";
            command.CommandText = query;
            command.Connection = conn;
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                credit_amt = int.Parse(reader[0].ToString());
                amount_due = int.Parse(reader[1].ToString());
            }

            amount_due += total_amount;
            if (amount_due > credit_amt)
            {
                MessageBox.Show("Warning : Credit limit crossed ");

            }
            reader.Close();
            query = "update retailer_credit set AMOUNT_DUE='" + amount_due + "' where RNAME='" + Form3.retailer_purchaselist[0].RNAME + "';";
            command.CommandText = query;
            command.Connection = conn;
            command.ExecuteNonQuery();
        }

        public static void UpdateStockDate()
        {
            int qty = 0;
            int qty_available = 0;
            string mname="";
            string date = "";
            command.Connection = conn;

            query = "select QTY,MNAME,MDATE from temp";
            command.CommandText = query;
            OleDbDataReader reader = command.ExecuteReader();

            while (reader.Read())
            { 
                qty=int.Parse(reader[0].ToString());
                mname=reader[1].ToString();
                date = reader[2].ToString();
            }

            reader.Close();

            
            DateTime curr_date=new DateTime(DateTime.Now.Year,DateTime.Now.Month,DateTime.Now.Day);
            query = "select count(*) from stock_date where SDATE=#" + curr_date + "#;";
            command.CommandText = query;
            int count=int.Parse(command.ExecuteScalar().ToString());
            if (count > 0)
            {
                query = "select CLOSING_STOCK from stock_date where MNAME='" + mname + "' and (((stock_date.MDATE)=#" + date + "#));";
                command.CommandText = query;
                reader = command.ExecuteReader();
                while (reader.Read())
                { 
                    qty_available=int.Parse(reader[0].ToString());
                }
                reader.Close();
                qty_available -= qty;
                query = "update stock_date set CLOSING_STOCK='" + qty_available + "' where MNAME='" + mname + "' and (((stock_date.MDATE)=#" + date + "#));";                                
            }

            else
            {
                int new_OpeningStock=0;
                query = "select CLOSING_STOCK from stock_date where MNAME='" + mname + "' and (((stock_date.MDATE)=#" + date + "#));";
                command.CommandText = query;
                reader = command.ExecuteReader();
                while (reader.Read())
                { 
                    new_OpeningStock=int.Parse(reader[0].ToString());
                }
                reader.Close();
                int new_ClosingStock=new_OpeningStock - qty;
                query = "insert into stock_date(MNAME,MDATE,SDATE,OPENING_STOCK,CLOSING_STOCK) values ('" + mname + "','" + date + "','" + curr_date + "','" + new_OpeningStock+ "','" + new_ClosingStock + "'); ";
              
            }
            command.CommandText = query;
            command.ExecuteNonQuery();



            

            


        }

   
    }
}